package com.cskaoyan.batch;

import com.cskaoyan.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/11/17 14:42
 *
 * @author ciggar
 *
 * // 批处理的Demo
 */
public class BatchDemo {

    static Connection connection;
    static {
        connection = JDBCUtils.getConnection();
    }

    // 数量       for         statement       PreparedStatement
    // 1000       1224          844             867
    // 10000      7845          7687            7835
    // 10000(加参数) 8181       7200             105


    public static void main(String[] args) throws SQLException {

        int num = 10000;

        long t1 = System.currentTimeMillis();
        // foreach循环插入
        foreachInsert(num);

        long t2 = System.currentTimeMillis();

        // statement 批处理
        statementBatch(num);

        long t3 = System.currentTimeMillis();

        // preparedStatement批处理
        preparedStatementBatch(num);

        long t4 = System.currentTimeMillis();


        System.out.println("for循环的时间：" + (t2-t1) + " 毫秒");
        System.out.println("statement批处理的时间：" + (t3-t2) + " 毫秒");
        System.out.println("PreparedStatement批处理的时间：" + (t4-t3) + " 毫秒");
    }


    // 逐条处理
    public static void foreachInsert(int nums) throws SQLException {

        for (int i = 0; i < nums; i++) {
            Statement statement = connection.createStatement();
            String name = "foreach:" + i;
            String sql = "insert into t_user values (null,'"+name+"',null)";
            statement.executeUpdate(sql);

            statement.close();
        }
    }


    // statement
    public static void statementBatch(int nums) throws SQLException {

        Statement statement = connection.createStatement();

        for (int i = 0; i < nums; i++) {
            String name = "statementBatch:" + i;
            String sql = "insert into t_user values (null,'"+name+"',null)";
            statement.addBatch(sql);
        }

        statement.executeBatch();

    }

    // PreparedStatement
    public static void preparedStatementBatch(int nums) throws SQLException {

        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_user values (null,?,null)");

        for (int i = 0; i < nums; i++) {
            preparedStatement.setString(1,"preparedStatementBatch:" + i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
    }

}
